<?
/**
 * @author Leonardo Alberto Celis
 * @name Oh!DB
 * @desc TDG (Table Data Gateway) for ADODB
 * @copyright 2005-2007 (c) Leonardo Alberto Celis
 * @blog http://leocelis.blogspot.com
 * @email celisla@hotmail.com
 *
 * Thanks to:
 * Mario Garcia: for find the being of this class and for put some good features
 * Federico Bergero: for find some catastrofics errores
 * Juan Cruz Borba: for find some catastrofics errores
 *
 * Related:
 * Martin Fowler: http://www.martinfowler.com/eaaCatalog/tableDataGateway.html
 *
 *
 * @todo
 * Make a Singleton, the schema could be in memory for every object table
 * Puts some examples in all the methods
 * Relocate all the names from the data model to properties
 * Multiple objects intances can interact with their own joins and filters
 *
 * @features
 * 13:14 25/06/2007 object suppor in addOrder method
 * 13:14 25/06/2007 setters and getters
 * 13:14 25/06/2007 addFunction for sum, max, min
 * 12:56 19/06/2007 add the filters of all de objects that is joined with this
 * 12:54 11/05/2007 join to oh!db objects support
 *
 * @bugs
 * 11:34 29/06/2007 Federico Bergero: if the value is empty, don't unset the field
 * 11:33 29/06/2007 Federico Bergero: if the field type is int and the send value is 0, set to null
 * 16:59 27/06/2007 Zend Code Analyzer free bugs
 * 14:00 27/06/2007 Federico Bergero: bug in buildFields, the secondary fields have precedence
 * 11:01 26/06/2007 Juan Cruz Borba: bug in buildFields and getMetaColumns methods, the fields in the object table must have
 * the "table." prefix only in the buildFields method.
 * 14:53 22/06/2007 still catastrofics errors in save method, now is fine
 * 18:38 20/06/2007 error with save method, the condition for insert/update was wrong
 * 15:00 20/06/2007 catastrofic error with save method, this convert the blank into null
 * 11:16 19/06/2007 buildJoins bug, if the table is an object the method add the logic field filter and the
 * left join become useless
 * 13:30 14/06/2007 Federico Bergero: bug in ohdb::buildFilters - precedence in logic field with other filters
 * 10:44 p.m. 05/06/2007 fix compatibility with logic field in save method
 * 09:44 p.m. 05/06/2007 fix compatibility with older version that doesn't have metacolumns
 * 09:29 05/06/2007 fix delete logic bug, delete the values of all the fields
 *
 */

class ohdb
{
	// the ADODB object connection

	public $oADODB					= "";
	public $Table					= "";
	public $PrimaryKey				= "";
	public $Order					= "";
	public $joins					= array();
	public $filters					= "";
	public $functions				= "";
	public $Debug					= false;
	public $LogicField     			= "";
	public $ChildsTables   			= array();
	public $xmlSQL         			= "";
	public $sql						= "";
	public $bCache					= false;
	public $nCacheTime				= 0;
	public $bFirstRegister			= false;
	public $bLastRegister			= false;
	public $bLogic					= false;
	// to upper
	public $bUpper 					= false;
	// validations
	public $RelatedTables   		= array();
	// check relations
	public $sCodeErrorRelations 	= "";
	//check for this fields
	public $sDuplicatedField 		= "";
	public $sCodeErrorDuplicated 	= "";
	// navigations constants
	public $nav_first 				= 1;
	public $nav_next 				= 2;
	public $nav_prev 				= 4;
	public $nav_last 				= 8;
	public $Limit 					= 0;
	public $Offset					= 0;
	public $iLastID					= 0;
	// private properties
	public $Fields					= "";
	private $DefaultField   		= "";
	// MetaData array
	protected $aMetaColumns			= "";
	// MetaRules array
	protected $aMetaRules			= "";
	// Fields array
	private $aFields				= "";
	// String join
	protected $sJoinFields			= "";
	// xml dom object
	public $oDomIt 					= null;
	// validation object
	public $oValidation 			= null;
	// recycled
	public $oRecycled 				= null;

	// construct and destruct!
	function __construct($oADODB = null)
	{
		$this->oADODB = $oADODB;
	}

	function __destruct()
	{

	}


	/**
	 * =======================================================
	 * Connection
	 * =======================================================
	 */

	/**
	 * @author Leonardo Alberto Celis
	 * @desc ADODB Connection
	 *
	 * @todo handled multiple objects connections
	 *
	 */
	function connectTo()
	{
		// by default take a global ADODB object
		if ($this->oADODB == '')
		{
			global $gobjADODB;
			$this->oADODB = $gobjADODB;
		}

		// if debug is active show the error message
		if ($this->Debug)
		{
			$this->oADODB->debug = true;
		}

		return $this->oADODB;
	}


	/**
	 * =======================================================
	 * Setters and Getters
	 * =======================================================
	 */

	/**
	 * @author Leonardo Alberto Celis
	 * @desc set the table
	 *
	 * @param unknown_type $psTableName
	 */
	public function setTable($psTableName)
	{
		$this->Table = $psTableName;
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc set the primary key
	 *
	 * @param unknown_type $psPrimaryKey
	 */
	public function setPrimaryKey($psPrimaryKey)
	{
		$this->PrimaryKey = $psPrimaryKey;
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc get all the selected rows by filters and joins
	 *
	 */
	function getAll()
	{
		$sSQL = 'SELECT ';

		$sSQL .= self::buildFields();

		$sSQL .= " FROM ".$this->Table;

		return self::RunSQL($sSQL);
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc get max id
	 *
	 */
	function getMaxID()
	{
		self::clean();
		return self::RunSQL("SELECT MAX(" . $this->PrimaryKey . ") AS MaxID FROM " . $this->Table)->fields['MaxID'];
	}


	/**
	 * @author Leonardo Alberto Celis
	 * @desc get min id
	 *
	 */
	function getMinID()
	{
		self::clean();
		return self::RunSQL("SELECT MIN(" . $this->PrimaryKey . ") AS MinID FROM " . $this->Table)->fields['MinID'];
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc get one row by id
	 *
	 */
	public function getOne($id)
	{
		self::cleanFilters();

		self::addFilter($this->PrimaryKey, "=", $id);

		return self::getAll();
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc get the first row
	 *
	 */
	function getFirst()
	{
		self::clean();
		$sSQL = "SELECT * FROM ".$this->Table . " order by " . $this->PrimaryKey . " LIMIT 0,1";
		$this->Limit = 0;

		return self::RunSQL($sSQL);
	}


	/**
	 * @author Leonardo Alberto Celis
	 * @desc count rows
	 *
	 */
	function getCount()
	{
		self::clean();
		$sSQL = "SELECT COUNT(*) AS COUNT FROM ".$this->Table;

		$rs = self::RunSQL($sSQL);

		if ($rs->EOF)
		{
			return 0;
		}
		else
		{
			return $rs->fields['COUNT'];
		}
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc sum all the values from the given field
	 *
	 */
	function getSum($field)
	{
		//$this->clean();
		//$sSQL = "SELECT SUM(" . $field . ") AS TOTAL FROM ".$this->Table;
		self::addFunction($field, $field, "SUM");
		//return self::RunSQL($sSQL);
	}

	/**
	 * @name Mario Garcia
	 * @desc Get the recordset schema
	 *
	 */
	function getRecordsetSchema($poRs)
	{
		$arrFields = array();

		for($i=0; $i < $poRs->_numOfFields; $i++ )
		{
			$arrFields[$poRs->FetchField($i)->name] = "";
		}

		return array($arrFields);
	}

	/**
	 *     @author Emanuel Goette.
	 *
	 *	  @desc
	 *
	 *     $primerRegistro: el registro donde empiesa a mostrar los datos
	 *
	 *     $cantidad: cantidad de datos a mostrar.
	 *
	 */
	function getAllPage($primerRegistro, $cantidad)
	{

		$sql = "SELECT * FROM ".$this->Table;

		$connect = self::connectTo();

		$sql = self::buildJoins($sql);

		$sql = self::buildFilters($sql);

		$sql = self::buildOrder($sql);

		$sql = $sql." limit ".$primerRegistro." , ".$cantidad;

		$rs = $connect->Execute($sql);

		if (!$rs)
		{
			// if debug is active show the error message
			if ($this->Debug)
			{
				print $connect->ErrorMsg();
			}
		}
		else
		{
			return $rs;
			//$rs->Close();
		}

		return array();

	}

	// spanish version
	public function getAllPagina($firstRegister, $count)
	{
		return self::getAllPage($firstRegister, $count);
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc get rows, but not a rs object, an array!
	 *
	 */
	function getOneToArray($id)
	{

		$rs = self::getOne($id);

		if ($rs->EOF)
		{
			return array();
		}
		else
		{
			return $rs->GetRows();
		}
	}
	function getAllToArray()
	{
		$rs = self::getAll();

		if ($rs->EOF)
		{
			return array();
		}
		else
		{
			return $rs->GetRows();
		}
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc set the fields to retrieve
	 *
	 * separate by comas
	 */
	public function setFields($string)
	{
		$this->Fields = $string;
	}


	/**
	 * =======================================================
	 * Friendly methods
	 * =======================================================
	 */

	/**
	 * @author Leonardo Alberto Celis
	 * @desc add functions to fields
	 *
	 */
	public function addFunction($psField, $psAlias, $psFunction)
	{
		$this->functions[] = array($psField, $psAlias, $psFunction);
	}

	/**
	 * @author Mario Garcia
	 * @desc friendly method for add joins
	 *
	 * @param
	 * xSecondaryTable: join to secondary table (not this object table)
	 *
	 */
	function addJoin($psTableName, $paKeys = null, $paReturnFields = null, $psDirection = null, $xSecondaryTable = null)
	{
		// fields
		// if the pair paKeys is not defined
		if ($paKeys == null)
		{
			// primary $paKeys[0]
			// foreign $paKeys[0]
			if (is_object($psTableName))
			{
				if ($this->PrimaryKey == '')  die("Oh!DB: The primary key is not defined");

				$paKeys[0] = $this->PrimaryKey;
				$paKeys[1] = $this->PrimaryKey;
			}
			if (is_object($xSecondaryTable))
			{
				if ($xSecondaryTable->PrimaryKey == '' || $psTableName->PrimaryKey)  die("ohdb::addJoin - The primary key is not defined");

				$paKeys[0] = $xSecondaryTable->PrimaryKey;
				$paKeys[1] = $psTableName->PrimaryKey;
			}
		}

		// join secondary table
		if ($xSecondaryTable != null)
		{
			if (is_object($xSecondaryTable))
			{
				$paKeys[0] = $xSecondaryTable->Table . "." . $paKeys[0];
			}
			else
			{
				$paKeys[0] = $xSecondaryTable. "." . $paKeys[0];
			}
		}

		// $psTableName could be an another Oh!DB object
		$aJoin =  array (
		'table' => $psTableName,
		'primary' => $paKeys[0],
		'foreign' => $paKeys[1],
		'direction' => $psDirection,
		);

		$this->joins[] = $aJoin;

		// preserve the object table
		if (is_object($psTableName))
		{
			$sTable = $psTableName->Table;
		}
		else
		{
			$sTable = $psTableName;
		}

		// return fields
		if (is_array($paReturnFields))
		{
			//foreach($paReturnFields as $sKey => $sValue)
			foreach($paReturnFields as $sValue)
			{
				if (stripos($sValue, ".") === false )
				{
					$this->sJoinFields .= ",$sTable.$sValue";

				}
				else
				{
					$this->sJoinFields .= ",$sValue";
				}
			}
		}
		else
		{
			$this->sJoinFields .= ",{$sTable}.*";
		}
	}


	/**
	 * @author Mario Garcia
	 * @desc friendly method for add filters
	 *
	 */
	function addFilter($psField = null, $psCriteria, $psValue, $psTableName = null, $psOperador = null, $psFunction = null)
	{
		// by default the primarykey
		if (is_null($psField)) $psField = $this->PrimaryKey;

		if (is_null($psTableName))
		{
			$psTableName = $this->Table;
		}

		$aFilter = array (
		'field' => $psTableName.'.'.$psField,
		'condition' =>$psCriteria,
		'value' => $psValue
		);

		if(!empty($psOperador))
		{
			$aFilter['operator'] = $psOperador;
		}

		if(!empty($psFunction))
		{
			$aFilter['function'] = $psFunction;
		}

		$this->filters[] = $aFilter;

	}

	/**
	 * @author Mario Garcia
	 * @desc friendly method for add rules
	 *
	 */
	function addRules($psCriteria, $psFieldName, $psMessageError)
	{
		$this->aMetaRules[] = $psCriteria.','.$psFieldName.','.$psMessageError;
	}

	/**
	 * @author Mario Garcia
	 * @desc friendly method for add orders
	 *
	 */
	function addOrder($psTableName = null, $psField , $psDirection = null)
	{
		// if it's a object
		if (is_object($psTableName))
		{
			$psTableName = $psTableName->Table;
		}

		// if it's null
		if ($psTableName == null)
		{
			$psTableName = $this->Table;
		}

		$this->Order[] = $psTableName.'.'.$psField.' '.$psDirection;
	}


	/**
	 * =======================================================
	 * Transactions
	 * =======================================================
	 */

	/**
	 * @author Leonardo Alberto Celis
	 * @desc handled transactions
	 *
	 */
	public function startTransaction()
	{
		$connect = self::connectTo();

		$connect->StartTrans();
	}

	public function endTransaction()
	{
		$connect = self::connectTo();
		return $connect->CompleteTrans();
	}


	/**
	 * =======================================================
	 * Core
	 * =======================================================
	 */

	/**
	 * @author Leonardo Alberto Celis
	 * @desc this is the core baby!
	 *
	 */
	public function RunSQL($sql)
	{
		$connect = self::connectTo();

		$sql = self::buildJoins($sql);

		$sql = self::buildFilters($sql);

		$sql = self::buildOrder($sql);

		// if it has function used group by
		if ($this->functions != '')
		{
			$sql .= " GROUP BY " . $this->Table . "." . $this->PrimaryKey;
		}

		// if it the cache is on
		if ($this->bCache)
		{
			if ($this->Limit != 0 || $this->Offset != 0)
			{
				$rs = $connect->CacheSelectLimit($this->nCacheTime, $sql, $this->Limit, $this->Offset);
			}
			else
			{
				$rs = $connect->CacheExecute($this->nCacheTime, $sql);
			}

		}
		else
		{

			if ($this->Limit != 0 || $this->Offset != 0)
			{
				$rs = $connect->SelectLimit($sql, $this->Limit, $this->Offset);
			}
			else
			{
				$rs = $connect->Execute($sql);
			}
		}

		$this->sql = $sql;

		if (!$rs)
		{
			if ($this->Debug)
			{
				echo $connect->ErrorMsg();
				die();
			}
		}
		else
		{
			$this->AffectedRows = $connect->Affected_Rows();
			return $rs;
		}

		//		return $rs;

		//@author Mario Garcia
		// No tiene que retornar un array
		return array();
		// Aqui tiene que devolver el false

	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc build fields
	 *
	 */
	private function buildFields()
	{
		$sFunctions = $this->functions;

		// if the fields has functions
		if ($sFunctions != '')
		{
			for ($i = 0; $i < count($sFunctions); $i++)
			{
				/*[0] // field
				 [1] // alias
				 [2] // function*/

				$sSQL .= ", " . $sFunctions[$i][2] . "(" . $this->Table . "." . $sFunctions[$i][0] . ") as " . $sFunctions[$i][1] . " ";
			}

			//$sSQL .= $sSQL . ", ";
			$sSQL = substr($sSQL,1);
			$sSQL .= ", ";
		}

		if ($this->Fields != '')
		{
			//if (!is_array($this->aFields))
			//{
			$aFieldsTmp = split(",", $this->Fields);

			$sFields = "";
			//foreach($aFieldsTmp as $sKey => $aValue)
			foreach($aFieldsTmp as $aValue)
			{
				$sFields .= "," . trim($this->Table) . "." . trim($aValue) . " ";
			}

			$this->Fields = substr($sFields,1);
			//}

			$sSQL .= $this->Fields;
		}
		else
		{
			$sSQL .= $this->Table . '.*';
		}

		// secondary fields
		if ($this->sJoinFields != '' && $this->sJoinFields != null)
		{
			$sSQL .= $this->sJoinFields;
		}

		return $sSQL;
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc Build the SQL statement based on relations with other tables
	 *
	 * @updated The table element could be an object, so is posibble to join to
	 * another Oh!DB objects
	 *
	 */
	public function buildJoins($sql)
	{
		$joins = $this->joins;

		if (count($joins) > 0 && $joins != '')
		{
			foreach ($joins as $join)
			{
				if (is_object($join['table']))
				{
					$sJoinTable = $join['table']->Table;

					// if the join object has filters bring them to this object
					$filters = $join['table']->filters;

					if ($filters != '' && count($filters) > 0)
					{
						// loop for all the filters and add to this object
						for ($i = 0; $i < count($filters); $i++)
						{
							$this->filters[] = $filters[$i];
						}

					}
				}
				else
				{
					$sJoinTable = $join['table'];
				}

				// si viene un join
				if ($join['direction'] != null && $join['direction'] != '')
				{
					$sql .= ' ' . $join['direction'] . ' JOIN ' . $sJoinTable .' ';
				}
				else
				{
					//$sql .= ' INNER JOIN ' . $join['table'] .' ';
					$sql .= ' INNER JOIN ' . $sJoinTable .' ';
				}

				if (isset($join['primary']))
				{

					// mitabla as tbl
					// Uso de Alias en la tabla
					if( stripos($sJoinTable ,' as ') > 0 )
					{
						$sJoinTable = substr( $sJoinTable , stripos($sJoinTable ,' as ') + 3);

					}

					$table = $this->Table;
					if (stripos($join['primary'], ".") !== false ) $table = "";
					$sql .= ' ON ' . $table . '.' . $join['primary'] . '=' . $sJoinTable . '.' . $join['foreign'] . ' ';
				}
			}
		}

		return $sql;
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc Build the filters
	 *
	 */
	public function buildFilters($sql)
	{
		$filters = $this->filters;

		if (($filters != '' && count($filters) > 0) || $this->bLogic) $sql .= " WHERE ";

		if ($this->bLogic)
		{
			$sql .= $this->Table . "." . $this->LogicField . " = 1 ";

			if ($filters != '' && count($filters) > 0)
			{
				$sql .= " AND ";
			}
		}

		$filters = $this->filters;

		if ($filters != '' && count($filters) > 0)
		{

			if ($this->bLogic)
			{
				$sql .= " ( ";
			}


			$i = 0;

			foreach ($filters as $filter)
			{
				if ($filter['function'] != '' ||  $filter['function'] != null)
				{
					$sql .= " " . $filter['function'] . '(' . $filter['field'] . ") " . $filter['condition'] . " '" . $filter['value'] . "'";
				}
				else
				{
					$sql .= " " . $filter['field'] . " " . $filter['condition'] . " '" . $filter['value'] . "'";
				}

				if ($i < count($filters)-1)
				{
					if (isset($filter['operator']) && ($filter['operator'] != '' || $filter['operator'] != null))
					{
						$sql .= ' ' . $filter['operator'];
					}
					else
					{
						$sql .= ' AND';
					}
				}

				$i++;
			}

			if ($this->bLogic)
			{
				$sql .= " ) ";
			}
		}

		return $sql;
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc Build the orders
	 *
	 */
	public function buildOrder($sql)
	{
		if (is_array($this->Order))
		{
			$sql .= " ORDER BY ";
			$sField = "";

			foreach($this->Order as $sKey ){
				$sField .= ",$sKey" ;
			}

			$sql .= substr($sField,1);
		}

		return $sql;
	}


	/**
	 * =======================================================
	 * Meta
	 * =======================================================
	 */

	/**
	 * @author Mario Garcia
	 * @desc this method is cancel, the validation rules must be in another class
	 *
	 */
	function initMetaRules()
	{
		// example:
		//$this->arrRules[] = "required,varOriNombre,El varOriNombre es requerido.";

		/*foreach($this->aMetaColumns  as $sKey => $aMetaColumn)
		 {
		 if($aMetaColumn->primary_key) continue;

		 if($aMetaColumn->not_null )
		 {
		 $this->aMetaRules[] = "required,{$aMetaColumn->name}, Es requerido.";
		 }

		 switch($aMetaColumn->type)
		 {
		 case 'int':
		 $this->aMetaRules[] = "digits_only,{$aMetaColumn->name},Ingrese un valor numerico.";
		 break;

		 //decimal,text,tinyint,date?
		 }
		 }*/
		return null;
	}

	/**
	 * @author Mario Garcia
	 * @desc this is awesome!, get the schema for the given table
	 *
	 */
	function getMetaColumns()
	{

		// pacth for store procedure sync error
		//if( is_object(self::connectTo()->_queryID) ) mysqli_free_result(self::connectTo()->_queryID);
		$dataDictionary = NewDataDictionary(self::connectTo());

		if(!$this->Table) return array();

		$aMetaColumns =  $dataDictionary->MetaColumns($this->Table);

		$aFields = array();
		$sFields = "";

		if (is_array($aMetaColumns))
		{
			//foreach($aMetaColumns as $sKey => $aValue)
			foreach($aMetaColumns as $aValue)
			{
				$aFields[] =  $aValue->name;
				//$sFields .= ",$this->Table.$aValue->name";
				$sFields .= ",$aValue->name";
				if($aValue->primary_key){
					$this->PrimaryKey = $aValue->name;
				}

			}
		}

		$this->aFields = $aFields;

		$this->Fields = substr($sFields,1);

		$this->aMetaColumns = $aMetaColumns;

		return $this->aMetaColumns;
	}

	/**
	 * @author Mario Garcia
	 * @desc init the meta, get the schema, empowered the object
	 *
	 */
	function initMeta()
	{
		//Este metodo carga en $this->aMetaColumns toda la info de la metadata
		//Tiene que ser llamado despues de inicializar $this->Table = "tblProOrigenes";
		//y tiene que ser llamado solo en el constructor
		self::getMetaColumns();
		//luego de crear la metadata creo las metas rules
		self::initMetaRules();
	}


	/**
	 * =======================================================
	 * Write actions
	 * =======================================================
	 */

	/**
	 * @author Leonardo Alberto Celis
	 * @desc the other core!
	 *
	 * @todo Unified save and RunSQL methods
	 *
	 */
	function save($data)
	{
		//if (is_array($this->aFields) && $data[$this->LogicField] != '0')
		if (is_array($this->aFields))
		{
			$aSave = null;

			foreach($this->aFields as $lnKey)
			{
				// if exists in the save array
				if (isset($data[$lnKey]))
				{
					$sType = $this->aMetaColumns[strtoupper($lnKey)]->type;
						
					// upper case
					if (($sType == 'char' || $sType == 'longtext' || $sType == 'mediumtext' || $sType == 'text'
					|| $sType == 'tinytext' || $sType == 'varchar' ) && $this->bUpper)
					{
						$this->str2Upper($data[$lnKey]);
					}
						
					// if it's int and the value is 0, set to null
					if (($sType == 'int' || $sType == 'tinyint' || $sType == 'bigint' || $sType == 'decimal'
					|| $sType == 'double' || $sType == 'float' || $sType == 'mediumint' || $sType == 'numeric'
					|| $sType == 'smallint' ) && $data[$lnKey] == '')
					{
						$data[$lnKey] = "null";
					}

					$aSave[$lnKey] = $data[$lnKey];
				}

			}

			if (!is_null($aSave)) $data = $aSave;
		}

		// if the logic delete is active;
		if ($this->bLogic)
		{
			if ($data[$this->LogicField] != '0')
			{
				$data[$this->LogicField] = '1';
			}
		}

		// connection
		$connect = self::connectTo();

//		if ($connect->databaseType == 'mysqlt')
//		{
//			$this->startTransaction();
//		}

		$ok = true;

		if (isset($data[$this->PrimaryKey]) && $data[$this->PrimaryKey] != "null")
		{
			// update
			$ok = $connect->replace($this->Table, $data, $this->PrimaryKey, true);
			$this->iLastID = $data[$this->PrimaryKey];

		}
		else
		{
			// insert
			$ok = $connect->AutoExecute($this->Table, $data, 'INSERT');
			$this->iLastID = $connect->Insert_ID();
		}
		
//		if ($connect->databaseType == 'mysqlt')
//		{
//			$this->endTransaction();
//		}

		return $ok;
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc delete the row
	 *
	 */
	//function delete($id, $data = null)
	function delete($id)
	{
		// logic delete
		if ($this->bLogic)
		{
			//$this->deleteLogic($id, $data);
			self::deleteLogic($id);
		}
		else
		{
			self::clean();
			$sSQL = "DELETE FROM ".$this->Table." WHERE ".$this->PrimaryKey." = ".$id;

			return self::RunSQL($sSQL);
		}

		return array();
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc delete the selected rows by filters
	 *
	 */
	public function deleteAll()
	{
		self::cleanJoins();
		$sSQL = "DELETE FROM " . $this->Table;

		return self::RunSQL($sSQL);
	}


	/**
	 * @author Leonardo Alberto Celis
	 * @desc logic delete
	 *
	 */
	//function deleteLogic($id, $data)
	function deleteLogic($id)
	{
		$data[$this->PrimaryKey] = $id;
		$data[$this->LogicField] = '0';

		self::save($data);

		self::cascadeDeleteLogic($this, $this->PrimaryKey, $id);
	}


	/**
	 * @author Leonardo Alberto Celis
	 * @desc Cascade logic delete
	 *
	 * @todo Change the Execute to RunSQL
	 */
	function cascadeDeleteLogic($pobjTable, $pKey, $pId)
	{
		// get the adodb object connection
		$connect = self::connectTo();

		// set the logic field in 0
		$data[$this->LogicField] = '0';

		if (count($pobjTable->ChildsTables) > 0 && $pobjTable->ChildsTables != '')
		{
			foreach( $pobjTable->ChildsTables as $value )
			{
				$objTable = new $value;

				$objTable->addFilter($pKey, "=", $pId);

				$rsChilds = $objTable->getAll();

				if (!$rsChilds->EOF)
				{
					while (!$rsChilds->EOF)
					{
						$connect->AutoExecute($objTable->Table, $data, 'UPDATE', $pKey . '=' . $pId);

						$objTable->cascadeDeleteLogic($objTable, $objTable->PrimaryKey, $rsChilds->fields[$objTable->PrimaryKey], 0);

						$rsChilds->MoveNext();
					}
				}
			}
		}
	}


	/**
	 * @author Leonardo Alberto Celis
	 * @desc Retrieved from Recycled
	 *
	 * @todo This has to be in a Recycled object
	 *
	 */
	function retrieveFromRecycled($pobjTable, $pId)
	{
		$oRecycled = $this->oRecycled;

		$connect = self::connectTo();

		$data[$this->LogicField] = '1';

		if (count($pobjTable->ChildsTables) > 0 && $pobjTable->ChildsTables != '')
		{
			foreach( $pobjTable->ChildsTables as $value )
			{
				$objTable = new $value;

				$objTable->addFilter($pobjTable->PrimaryKey, "=", $pId);

				$rsChilds = $objTable->getAll();

				if (!$rsChilds->EOF)
				{
					while (!$rsChilds->EOF)
					{
						$oRecycled->addFilter('varPapTabla', '=', $objTable->Table, null, 'AND');
						$oRecycled->addFilter('varPapCampoClave', '=', $objTable->PrimaryKey, null, 'AND');
						$oRecycled->addFilter('intPapRegID', '=', $rsChilds->fields[$objTable->PrimaryKey], null);

						$rsDeleted = $oRecycled->getAll();

						if ($rsDeleted->EOF)
						{
							$connect->AutoExecute($objTable->Table, $data, 'UPDATE', $pobjTable->PrimaryKey . '=' . $pId);
						}

						$objTable->retrieveFromRecycled($objTable, $rsChilds->fields[$objTable->PrimaryKey]);

						$rsChilds->MoveNext();
					}
				}
			}
		}
	}


	/**
	 * =======================================================
	 * Validations
	 * =======================================================
	 */

	/**
	 * @author Leonardo Alberto Celis
	 * @desc check for duplicated rows
	 *
	 */
	public function checkDuplicated ($id = null, $name = null)
	{
		$o = $this;

		// check for duplicated register
		if ($this->sCodeErrorDuplicated != '' && $this->sDuplicatedField != '')
		{
			$o->addFilter($this->sDuplicatedField, '=', $name);
			// if the id is specified then check for all except the id
			if ($id != '') $o->addFilter($this->PrimaryKey, '<>', $id);

			// if the login field is set
			if ($this->bLogic)
			{
				$o->addFilter($this->LogicField, '=', '1');
			}

			$rs = $o->getAll();

			if (!$rs->EOF)
			{
				return false;
			}
			else
			{
				return true;
			}
		}

		return false;
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc Check if the register id exists
	 *
	 */
	function validateUnique($id)
	{
		self::clean();
		self::addFilter($this->PrimaryKey, "=", $id, null, null);
		$rs = self::getAll();
		self::clean();

		return !$rs->EOF;
	}

	/**
	 * @author Mario Garcia
	 * @desc validate metarules
	 *
	 */
	function validateMetaRules( $aValues)
	{
		return validateFields($aValues, $this->aMetaRules);
	}

	/**
	 * @name Leonardo Alberto Celis
	 * @desc check for relations in another tables
	 *
	 */
	public function checkRelations($id)
	{
		$ok = true;

		// check for relationed tables
		if (count($this->ChildsTables) > 0 && $this->ChildsTables != '')
		{
			foreach( $this->ChildsTables as $value )
			{
				$oTable = new $value;

				$oTable->addFilter($this->PrimaryKey, "=", $id, null, null);

				$rsChilds = $oTable->getAll();

				if (!$rsChilds->EOF)
				{
					$ok &= false;
				}
			}
		}

		// check for related tables
		if (count($this->RelatedTables) > 0 && $this->RelatedTables != '')
		{
			foreach( $this->RelatedTables as $value )
			{
				$oTable = new $value;

				$oTable->addFilter($this->PrimaryKey, "=", $id, null, null);

				$rsChilds = $oTable->getAll();

				if (!$rsChilds->EOF)
				{
					$ok &= false;
				}
			}
		}

		return $ok;
	}


	/**
	 * =======================================================
	 * Cleaning
	 * =======================================================
	 */

	/**
	 * @author Leonardo Alberto Celis
	 * @desc Clean objects properties
	 *
	 */
	function clean()
	{
		//$this->JoinFields = "";
		$this->Order  = "";
		$this->sJoinFields  = "";
		//$this->aFields = "";
		$this->Fields = "";
		$this->functions = "";
		$this->filters = "";
		$this->joins = "";

		/**
		 * @desc Patch: reload the schema
		 */
		//$this->initMeta();
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc Clean the filters
	 *
	 */
	function cleanFilters()
	{
		$this->filters = null;
		self::initMeta();
	}

	/**
	 * @author Leonaro Alberto Celis
	 * @desc remove the last apply filter
	 */
	public function removeLastFilter()
	{
		$this->filters = array_slice($this->filters, 0, count($this->filters)-1);
		self::initMeta();
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc Clean the joins
	 *
	 */
	function cleanJoins()
	{
		$this->joins = null;
		self::initMeta();
	}


	/**
	 * =======================================================
	 * Cool stuff
	 * =======================================================
	 */

	/**
	 * @author Leonardo Alberto Celis
	 * @desc Change to Upper only the alphabetical characters
	 *
	 */
	private function str2Upper($psString)
	{
		$psString = mb_convert_case($psString, MB_CASE_UPPER, "UTF-8");

		return $psString;
	}

	/**
	 * @author Mario Garcia
	 * @desc Execute a Store Procedure
	 *
	 */
	public function ExecuteSP($psSPName, $paSPParameters)
	{
		$lsSQLParm  = "";

		foreach ($paSPParameters as $lsValue)
		{
			//$lsSQLParm .= ",?";
			$lsSQLParm .= ",{$lsValue}";
		}
		$lsSQLParm = substr($lsSQLParm,1);

		$lsSQLSP = "CALL {$psSPName} ({$lsSQLParm})";
		$connect = self::connectTo();
		$lmRS = $connect->Execute($lsSQLSP, $paSPParameters);


		return $lmRS;
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc rows navigator
	 *
	 */
	public function navigator($piConst, $piID)
	{
		switch ($piConst)
		{
			case $this->nav_first:
				//$this->removeLastFilter();
				$this->Limit = 1;
				self::addOrder(null, $this->PrimaryKey, "ASC");
				$rs = self::getAll();

				if ($rs->EOF)
				{
					return self::getRecordsetSchema($rs);
				}
				else
				{
					//return $rs->getRows();
					return $rs;
				}
				break;

			case $this->nav_next:
				$this->Limit = 1;
				self::addFilter($this->PrimaryKey, ">", $piID);
				$rs = self::getAll();

				if ($rs->EOF)
				{
					$this->removeLastFilter();
					return self::navigator($this->nav_first, null);
				}
				else
				{
					//return $rs->GetArray(1);
					return $rs;
				}
				break;

			case $this->nav_prev:
				self::addFilter($this->PrimaryKey, "<", $piID);
				$rs = self::getAll();

				if ($rs->EOF)
				{
					$this->removeLastFilter();
					return self::navigator($this->nav_last, null);
				}
				else
				{
					$rs->MoveLast();
					//return $rs->GetArray(1);

					return $rs;
				}
				break;

			case $this->nav_last:
				//$this->removeLastFilter();
				$this->Limit = 1;
				self::addOrder(null, $this->PrimaryKey, "DESC");
				$rs = self::getAll();

				if ($rs->EOF)
				{
					return self::getRecordsetSchema($rs);
				}
				else
				{
					return $rs;
				}

				break;
		}

		return array();
	}

	/**
	 * @author Leonardo Alberto Celis
	 * @desc this is experimental, the xml query!
	 *
	 */
	function xmlQuery($pstrTag, $parrParams = array())
	{
		// the domit object, only this object!
		$success = $this->oDomIt->loadXML($this->xmlSQL, true);

		if ($success)
		{
			//use getElementsByTagName to gather all elements named "cd"
			$matchingNodes =& $this->oDomIt->getElementsByTagName($pstrTag);

			//if any matching nodes are found, echo to browser
			if ($matchingNodes != null)
			{
				$temp = $matchingNodes->item(0);
				$sql = $temp->getText();

				if (count($parrParams) > 0)
				{
					for ($i = 0; $i < count($parrParams); $i++)
					{
						$sql = str_replace('@param' . $i, $parrParams[$i], $sql);
					}
				}

				$connect = self::connectTo();
				return $connect->Execute($sql);
			}
			else
			{
				return false;
			}
		}
		else
		{
			return false;
		}
	}
}
